USE ILoveShoppingCart
GO
CREATE TABLE Product
(
    ProductID int PRIMARY KEY IDENTITY(1,1),
    ProductName varchar(100),
    ProductPrice float,
    ProductImgURL varchar(100),
    ProductAmount int DEFAULT 10,
	ProductDes varchar(20) DEFAULT 'acoustic' CHECK(ProductDes='acoustic' OR ProductDes='classic')
)
GO
CREATE TABLE [User]
(
    Username varchar(20) PRIMARY KEY,
    Password varchar(100),
    Fullname varchar(50),
    UserRole varchar(10) CHECK(UserRole = 'admin' OR UserRole = 'customer' OR UserRole = 'staff')    
)
GO
SELECT *FROM Product
SELECT *
FROM   (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS rownumber,* FROM Product) as vt_table  
WHERE  rownumber >=  + 5 AND rownumber <= 10
SELECT COUNT(ProductID) as Total FROM Product

INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-101',100,'images/1.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-102',100,'images/2.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-103',100,'images/3.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-104',100,'images/4.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-105',100,'images/5.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-106',100,'images/6.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-107',100,'images/7.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-108',100,'images/8.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-109',100,'images/9.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-110',100,'images/10.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-111',100,'images/11.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-112',100,'images/12.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-113',100,'images/13.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-114',100,'images/14.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-115',100,'images/15.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-116',100,'images/16.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-117',100,'images/17.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-118',100,'images/18.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-119',100,'images/19.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-120',100,'images/20.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-121',100,'images/21.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-122',100,'images/22.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-123',100,'images/23.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-124',100,'images/24.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-125',100,'images/25.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-126',100,'images/26.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-127',100,'images/27.jpg')
INSERT INTO Product(ProductName,ProductPrice,ProductImgURL) VALUES('Orion Handmade HD-127',100,'images/28.jpg')

INSERT INTO [User] VALUES('admin','123456','Dinh Tuan Long','admin')
INSERT INTO [User] VALUES('giza','123456','Dinh Tuan Long','staff')
INSERT INTO [User] VALUES('longdt','123456','Dinh Tuan Long','customer')

SELECT *FROM [User] WHERE Username='giza' AND [Password]='123456'
SELECT [ProductID],[ProductName],[ProductPrice],[ProductImgURL] 
FROM Product WHERE [ProductName] LIKE '%'+'12'+ '%'

SELECT COUNT(ProductID) as Total FROM [Product]
SELECT [Username],[Password],[Fullname],[UserRole] FROM [User] WHERE Username= 'giza' AND [Password]='1234a56'
SELECT *FROM [User] WHERE [Username]='admin' AND [Password]='123456'

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS rownumber, [Product].ProductID FROM Product) as my_table
WHERE rownumber >= 5 AND rownumber <=10
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS rownumber, * FROM Product) as my_table
WHERE rownumber >= 1 AND rownumber <=5 AND ProductName LIKE '%'+'1'+'%'

SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber, *FROM [Product] ) as pd_table 
WHERE RowNumber BETWEEN 1 AND 28
AND [pd_table].[ProductName] LIKE '%'+'12'+'%'

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber, *FROM [Product] WHERE Product.ProductName LIKE '%'+'12'+'%' ) as pd_table
WHERE RowNumber >=5AND RowNumber <= 8
